﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelToSqlserver
{
    public class ExcelACEHelper
    {
        /// <summary>
        /// Excel OLEDB的连接字符串
        /// </summary>
        public string ExcelConnectionString
        {
            get
            {
                return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0'";
            }
        }
        private string _fileName;
        /// <summary>
        /// Excel文件全名，带路径
        /// </summary>
        public string FileName
        {
            get
            {
                return _fileName;
            }
            set
            {
                _fileName = value;
            }
        }
        public DataTable Query(string QueryString, string TableName = "Sheet1$")
        {
            DataTable _dt;
            using (OleDbConnection oleConn = new OleDbConnection(ExcelConnectionString))
            {
                oleConn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(QueryString, oleConn);
                DataSet ds = new DataSet();
                da.Fill(ds, TableName);
                _dt = ds.Tables[TableName];
                oleConn.Close();
            }
            return _dt;
        }
        public string[] GetTables()
        {
            string[] tables;
            using (OleDbConnection oleConn = new OleDbConnection(ExcelConnectionString))
            {
                oleConn.Open();
                DataTable _dt = oleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                tables = new string[_dt.Rows.Count];
                for (int i = 0; i < _dt.Rows.Count; i++)
                {
                    tables[i] = _dt.Rows[i]["TABLE_NAME"].ToString().Trim();
                }
                oleConn.Close();
            }
            return tables;
        }

        public int Update(string QueryString, string TableName = "Sheet1$")
        {
            int Non = -1;
            using (OleDbConnection oleConn = new OleDbConnection(ExcelConnectionString))
            {
                oleConn.Open();
                OleDbCommand oleCmd = oleConn.CreateCommand();
                oleCmd.CommandText = QueryString;
                Non = oleCmd.ExecuteNonQuery();
                oleConn.Close();
            }
            return Non;
        }
        public void CreateFile(string FilePath, string sheet, DataTable D)
        {
            File.Copy("MO", FilePath, true);
            string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0'";
            using (OleDbConnection oleConn = new OleDbConnection(ConnectionString))
            {
                oleConn.Open();
                OleDbCommand oleCmd = oleConn.CreateCommand();
                oleCmd.CommandText = "CREATE TABLE [" + sheet + "] ([Tag] VarChar,[Column] VarChar,[Remark] VarChar)";
                oleCmd.ExecuteNonQuery();
                foreach (DataRow r in D.Rows)
                {
                    oleCmd.CommandText = string.Format("INSERT INTO [{0}$] ( [Tag] , [Column] , [Remark] ) VALUES ( '{1}' , '{2}' , '{3}' )", sheet.Trim(), r[0], r[1], r[2]);
                    oleCmd.ExecuteNonQuery();
                }
                oleConn.Close();
            }
        }
        public void AppendFile(string FilePath, string sheet, DataTable D)
        {
            string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=0'";
            using (OleDbConnection oleConn = new OleDbConnection(ConnectionString))
            {
                oleConn.Open();
                OleDbCommand oleCmd = oleConn.CreateCommand();
                oleCmd.CommandText = "CREATE TABLE [" + sheet.Trim() + "] ([Tag] VarChar,[Column] VarChar,[Remark] VarChar)";
                oleCmd.ExecuteNonQuery();
                foreach (DataRow r in D.Rows)
                {
                    oleCmd.CommandText = string.Format("INSERT INTO [{0}$] ( [Tag] , [Column] , [Remark] ) VALUES ( '{1}' , '{2}' , '{3}' )", sheet.Trim(), r[0], r[1], r[2]);
                    oleCmd.ExecuteNonQuery();
                }
                oleConn.Close();
            }
        }
    }
}
